﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace MakeIT.MiraPark.Negocios
{
    public static partial class ModelBO
    {

        public static CabineVO getCabine(int idCabine)
        {
            List<CabineVO> listaCabineVo = getCabines(idCabine);
            if (listaCabineVo != null && listaCabineVo.Count > 0)
                return listaCabineVo[0];
            else
                return null;
        }

        public static List<CabineVO> getCabines()
        {
            return getCabines(null);
        }

        public static List<CabineVO> getCabines(int? idCabine)
        {
            return getCabines(idCabine, null);
        }
        public static List<CabineVO> getCabines(int? idCabine, int? idPatio)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT c.IdCabine, c.Cabine, c.IdPatio, p.Patio ");
            query.AppendLine("FROM cabine c JOIN patio p ON c.IdPatio = p.IdPatio ");

            if (idPatio != null)
            {
                query.AppendLine(where + "c.IdPatio=?IdPatio ");
                where = " AND ";
            }
            if (idCabine != null)
            {
                query.AppendLine(where + "c.IdCabine=?IdCabine ");
                where = " AND ";
            }
            query.AppendLine("ORDER BY c.IdPatio, Cabine ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idCabine != null) cmd.Parameters.AddWithValue("?IdCabine", idCabine);
            if (idPatio != null) cmd.Parameters.AddWithValue("?IdPatio", idPatio);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<CabineVO> listCabine = new List<CabineVO>();

                while (reader.Read())
                {
                    CabineVO cabineVO = CabineVO.CreateCabineVO();
                    cabineVO.idCabine = reader.GetInt32("IdCabine");
                    cabineVO.cabine = reader.GetString("Cabine");
                    cabineVO.idPatio = reader.GetInt32("IdPatio");
                    cabineVO.patioVO.patio = reader.GetString("Patio");

                    listCabine.Add(cabineVO);
                }
                reader.Close();
                return listCabine;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setCabine(CabineVO cabineVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE cabine ");
            query.AppendLine("SET Cabine=?Cabine, IdPatio=?IdPatio ");
            query.AppendLine("WHERE IdCabine=?IdCabine ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdCabine", cabineVO.idCabine);
            cmd.Parameters.AddWithValue("?Cabine", cabineVO.cabine);
            cmd.Parameters.AddWithValue("?IdPatio", cabineVO.idPatio);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newCabine(CabineVO cabineVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO cabine ");
            query.AppendLine("SET Cabine=?Cabine, IdPatio=?IdPatio ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Cabine", cabineVO.cabine);
            cmd.Parameters.AddWithValue("?IdPatio", cabineVO.idPatio);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delCabine(CabineVO cabineVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM cabine ");
            query.AppendLine("WHERE IdCabine=?IdCabine ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdCabine", cabineVO.idCabine);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static bool readyToDeleteCabine(int idCabine)
        {
            int contagem = 0;

            MySqlDataReader reader = null;
            StringBuilder query = new StringBuilder();
            MySqlCommand cmd = getConnection();
            cmd.Connection.Open();

            try
            {

                query.AppendLine("SELECT count(*) contagem FROM movimentacao WHERE IdCabine=?IdCabine ");
                cmd.CommandText = query.ToString();
                cmd.Parameters.AddWithValue("?IdCabine", idCabine);
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem = reader.GetInt32("contagem");
                reader.Close();

            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

            return contagem == 0;

        }
    }
}
